package net.tycmc.bulb.note.core.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.ConnHelper;
import net.tycmc.bulb.common.dao.DetermineParamType;

import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.log4j.Logger;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.tycmc.functions.util.DynamicReadConfig;

public class MyConnectDSDAO {
	private static Logger log = Logger.getLogger(MyConnectDSDAO.class);
	String ServerName;
	int PortNumber;
	String UserName;
	String Password;
	String DatabaseName;
	
	public MyConnectDSDAO(){
		try {
			ServerName = DynamicReadConfig.getConfigProperty("config.properties", "ServerName");
			PortNumber = Integer.parseInt(DynamicReadConfig.getConfigProperty("config.properties", "PortNumber"));
			UserName= DynamicReadConfig.getConfigProperty("config.properties", "UserName");
			Password= DynamicReadConfig.getConfigProperty("config.properties", "Password");
			DatabaseName= DynamicReadConfig.getConfigProperty("config.properties", "DatabaseName");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public MyConnectDSDAO(String ServerName,int PortNumber,String UserName,String Password,String DatabaseName){
		this.ServerName = ServerName;
		this.PortNumber = PortNumber;
		this.UserName= UserName;
		this.Password= Password;
		this.DatabaseName= DatabaseName;
	}
	
	/**
	 * 数据库链接
	 * @return
	 */
	public Connection getConnection(){
		SQLServerDataSource ds = new SQLServerDataSource();
        ds.setUser(UserName);
        ds.setPassword(Password);
        ds.setServerName(ServerName);
        ds.setPortNumber(PortNumber); 
        ds.setDatabaseName(DatabaseName);
        try {
			return ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
        return null;
	}
	
	private String toString(String strSQL, Object[] params){
		StringBuilder sb = new StringBuilder();
		sb.append(strSQL);
		sb.append("\n");
		for(Object param:params){
			if(param instanceof String){
				sb.append((String)param);
			}else{
				sb.append(param);
			}
			sb.append("\t");
		}
		
		return sb.toString();
	}
	
	/**
	 * 数据更新
	 * @param strSQL
	 * @param params
	 * @return
	 */
	public int execUpdate(String strSQL,Object[] params){
		Connection con=null;
		PreparedStatement ps = null;
		try {
			con = this.getConnection();	//链接数据库
			
			ps = con.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			log.error(this.toString(strSQL, params), e);
		}finally {
			ConnHelper.close(ps);
			ConnHelper.close(con);
	    }
		return -1;
	}
	
	/**
	 * 数据插入
	 * @param strSQL
	 * @param params
	 * @return
	 */
	public int execInsert(String strSQL,Object[] params){
		Connection con=null;
		PreparedStatement ps = null;
		try {
			con = this.getConnection();	//链接数据库
			
			ps = con.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(con);
		}
		
		return -1;
	}
	
	/**
	 * 多数据查询
	 * @param strSQL
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> execQuery(String strSQL,Object[] params){
		Connection con=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String,Object>> rsList = new ArrayList<Map<String,Object>>(1);
		
		try {
			con = this.getConnection();	//链接数据库
			
			ps = con.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			while(rs.next()){
				int cc = metaData.getColumnCount();
				Map<String,Object> mapRS =new CaseInsensitiveMap();// new HashMap<String,Object>();
				for(int i=1;i<=cc;i++){
					mapRS.put(metaData.getColumnName(i), rs.getObject(i));
				}
				rsList.add(mapRS);
			}
			
			return rsList;
		} catch (Exception e) {
			e.printStackTrace();
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(con);
			ConnHelper.close(rs);
		}
		
		return rsList;
	}
	
	/**
	 * 单数据查询
	 * @param strSQL
	 * @param params
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String,Object> execQueryOne(String strSQL,Object[] params){
		Connection con=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();	//链接数据库
			
			ps = con.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}
			
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			if(rs.next()){
				int cc = metaData.getColumnCount();
				Map<String,Object> mapRS = new CaseInsensitiveMap();//new HashMap<String,Object>();
				for(int i=1;i<=cc;i++){
					mapRS.put(metaData.getColumnName(i), rs.getObject(i));
				}
				return mapRS;
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(con);
			ConnHelper.close(rs);
		}
		
		return new HashMap<String,Object>();
	}
	
	/**
	 * 查询数据量
	 * @param strSQL
	 * @param params
	 * @return
	 */
	public int getCount(String strSQL,Object[] params){
		Connection con=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			con = this.getConnection();	//链接数据库
		
			ps = con.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				DetermineParamType.determine(ps, i+1, params[i]);
			}
			
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}	
		} catch (Exception e) {
			e.printStackTrace();
			log.error(this.toString(strSQL, params), e);
		}finally{
			ConnHelper.close(ps);
			ConnHelper.close(con);
			ConnHelper.close(rs);
		}
		return -1;
	}
	
	public static void main(String[] args) {
		MyConnectDSDAO d = new MyConnectDSDAO();
		String SQL = "select count(*) con from VclDB2_201410 where VclDB2_ID>?";
		int list = d.getCount(SQL, new Object[]{0});
		System.out.println(list);
		
		String SQL3 = "select top 1 * from VclDB2_201410 where VclDB2_ID=?";
		Map<String,Object> list3 = d.execQueryOne(SQL3, new Object[]{3});
		System.out.println(list3.get("VclDB2_Vcl_ID"));
		
//		String SQL = "select * from VclDB2_201410 where VclDB2_ID>?";
//		List<Map<String,Object>> list = d.execQuery(SQL, new Object[]{3});
//		System.out.println(list.size());
		
//		String SQL = "update VclDB2_201410 set VclDB2_Vcl_ID=? where VclDB2_ID=?";
//		int list = d.execUpdate(SQL,new Object[]{-2,1});
//		System.out.println(list);
		
//		String SQL2 = "insert into VclDB2_201410 values(?,?,?,?,getdate())";
//		int list2 = d.execUpdate(SQL2,new Object[]{66,66,66,66});
//		System.out.println(list2);
		
	}
}
